﻿using DocumentFormat.OpenXml;
using Excel = DocumentFormat.OpenXml.Office2010.Excel;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Text.RegularExpressions;

namespace Vision.Utilities.VisualStudioTool
{
    public class OpenXmlExcel : IDisposable
    {
        #region Ctor
        public OpenXmlExcel(string destinationFileFullPath, string templateFileFullPath)
        {
            DestinationFileFullPath = destinationFileFullPath;
            File.Copy(templateFileFullPath, DestinationFileFullPath, true);
            Document = SpreadsheetDocument.Open(DestinationFileFullPath, true);            
            WbPart = Document.WorkbookPart; 
            var wbsp = WbPart.WorkbookStylesPart;
            // add styles to sheet
            wbsp.Stylesheet = CreateStylesheet();
            wbsp.Stylesheet.Save();
        }
        #endregion

        #region Properties

        public WorkbookPart WbPart { get; private set; }
        public SpreadsheetDocument Document { get; private set; }
        public String DestinationFileFullPath { get; private set; }
        #endregion

        #region Public Methods 
        /// <summary>
        /// Update Value
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="addressName"></param>
        /// <param name="value"></param>
        /// <param name="styleIndex"></param>
        /// <param name="valueType"></param>
        /// <returns></returns>
        public bool UpdateValue(string sheetName, string addressName, string value,
                              uint styleIndex = 0, CellValues valueType = CellValues.SharedString)
        {
            // Assume failure.
            var updated = false;

            var sheet = WbPart.Workbook
                              .Descendants<Sheet>()
                              .Where((s) => s.Name == sheetName)
                              .FirstOrDefault();

            if (sheet != null)
            {
                var ws = ((WorksheetPart)(WbPart.GetPartById(sheet.Id))).Worksheet;
                var cell = GetOrInsertCellInWorksheet(ws, addressName);

                switch (valueType)
                {
                    case CellValues.SharedString:
                        // Either retrieve the index of an existing string,
                        // or insert the string into the shared string table
                        // and get the index of the new item.
                        int stringIndex = InsertSharedStringItem(WbPart, value);

                        cell.CellValue = new CellValue(stringIndex.ToString());
                        cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                        break;
                    default:
                        cell.CellValue = new CellValue(value);
                        cell.DataType = new EnumValue<CellValues>(valueType);
                        break;
                }
                if (styleIndex >= 0)
                    cell.StyleIndex = styleIndex;

                // Save the worksheet.
                ws.Save();
                updated = true;
            }

            return updated;
        }
        public bool UpdateValueWithFormula(string sheetName, string addressName, string formulaText,
                                           uint styleIndex = 0)
        {
            // Assume failure.
            var updated = false;

            var sheet = WbPart.Workbook
                              .Descendants<Sheet>()
                              .Where((s) => s.Name == sheetName)
                              .FirstOrDefault();

            if (sheet != null)
            {
                var ws = ((WorksheetPart)(WbPart.GetPartById(sheet.Id))).Worksheet;
                var cell = GetOrInsertCellInWorksheet(ws, addressName);

                cell.CellFormula = new CellFormula(formulaText);

                if (styleIndex >= 0)
                    cell.StyleIndex = styleIndex;

                // Save the worksheet.
                ws.Save();
                updated = true;
            }

            return updated;
        }

        /// <summary>
        /// This method is used to force a recalculation of cells containing formulas. The
        /// CellValue has a cached value of the evaluated formula. This
        /// prevents Excel from recalculating the cell even if 
        /// calculation is set to automatic.
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="addressName"></param>
        /// <returns></returns>
        public bool RemoveCellValue(string sheetName, string addressName)
        {
            var returnValue = false;

            var sheet = WbPart.Workbook.Descendants<Sheet>().
                Where(s => s.Name == sheetName).FirstOrDefault();
            if (sheet != null)
            {
                var ws = ((WorksheetPart)(WbPart.GetPartById(sheet.Id))).Worksheet;
                var cell = GetOrInsertCellInWorksheet(ws, addressName);

                // If there is a cell value, remove it to force a recalculation
                // on this cell.
                if (cell.CellValue != null)
                {
                    cell.CellValue.Remove();
                }

                // Save the worksheet.
                ws.Save();
                returnValue = true;
            }

            return returnValue;
        }

        public void MergeCells(string sheetName, string startCellAddress, string endCellAddress)
        {
            var sheet = WbPart.Workbook.Descendants<Sheet>().
                                   Where(s => s.Name == sheetName)
                                   .FirstOrDefault();
            if (sheet == null || string.IsNullOrEmpty(startCellAddress) || string.IsNullOrEmpty(endCellAddress))
            {
                return;
            }

            var worksheet = ((WorksheetPart)(WbPart.GetPartById(sheet.Id))).Worksheet;

            // Verify if the specified cells exist, and if they do not exist, create them.
            GetOrInsertCellInWorksheet(worksheet, startCellAddress);
            GetOrInsertCellInWorksheet(worksheet, endCellAddress);

            // Create the merged cell and append it to the MergeCells collection.
            MergeCell mergeCell = new MergeCell() { Reference = new StringValue(startCellAddress + ":" + endCellAddress) };

            MergeCells mergeCells;
            if (worksheet.Elements<MergeCells>().Count() > 0)
            {
                mergeCells = worksheet.Elements<MergeCells>().First();
                mergeCells.Append(mergeCell);
            }
            else
            {
                mergeCells = new MergeCells();
                mergeCells.Append(mergeCell);

                // Insert a MergeCells object into the specified position.
                if (worksheet.Elements<CustomSheetView>().Any())
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<CustomSheetView>().First());
                }
                else if (worksheet.Elements<DataConsolidate>().Any())
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<DataConsolidate>().First());
                }
                else if (worksheet.Elements<SortState>().Any())
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<SortState>().First());
                }
                else if (worksheet.Elements<AutoFilter>().Any())
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<AutoFilter>().First());
                }
                else if (worksheet.Elements<Scenarios>().Any())
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<Scenarios>().First());
                }
                else if (worksheet.Elements<ProtectedRanges>().Any())
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<ProtectedRanges>().First());
                }
                else if (worksheet.Elements<SheetProtection>().Any())
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetProtection>().First());
                }
                else if (worksheet.Elements<SheetCalculationProperties>().Any())
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetCalculationProperties>().First());
                }
                else if (worksheet.Elements<SheetData>().Any())
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
                }
                else if (worksheet.Elements<OpenXmlCompositeElement>().Any())
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<OpenXmlCompositeElement>().First());
                }
                else
                {
                    worksheet.InsertAt(mergeCells, 0);
                }
            }
            
            worksheet.Save();
        }

        public void Execute(string sheetName, Action<Worksheet> action)
        {
            var sheet = WbPart.Workbook.Descendants<Sheet>().
                                    Where(s => s.Name == sheetName)
                                    .FirstOrDefault();
            if (sheet == null)
            {
                return;
            }

            var worksheet = ((WorksheetPart)(WbPart.GetPartById(sheet.Id))).Worksheet;

            action(worksheet);
        }
        #endregion

        #region Helper Methods

        /// <summary>
        /// Given a Worksheet and an address (like "AZ254"), either return a 
        /// cell reference, or create the cell reference and return it.
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="addressName"></param>
        /// <returns></returns>
        private Cell GetOrInsertCellInWorksheet(Worksheet ws, string addressName)
        {
            var sheetData = ws.GetFirstChild<SheetData>();
            var cell = default(Cell);

            var rowNumber = GetRowIndex(addressName);
            var row = GetRow(sheetData, rowNumber);

            // If the cell you need already exists, return it.
            // If there is not a cell with the specified column name, insert one.  
            var refCell = row.Elements<Cell>()
                             .Where(c => c.CellReference.Value == addressName)
                             .FirstOrDefault();
            if (refCell != null)
            {
                cell = refCell;
            }
            else
            {
                cell = CreateCell(row, addressName);
            }
            return cell;
        }

        /// <summary>
        /// Add a cell with the specified address to a row.
        /// </summary>
        /// <param name="row"></param>
        /// <param name="address"></param>
        /// <returns></returns>
        private Cell CreateCell(Row row, String address)
        {
            var cellResult = default(Cell);
            var refCell = default(Cell);

            // Cells must be in sequential order according to CellReference. 
            // Determine where to insert the new cell.
            foreach (var cell in row.Elements<Cell>())
            {
                if (string.Compare(cell.CellReference.Value, address, true) > 0)
                {
                    refCell = cell;
                    break;
                }
            }

            cellResult = new Cell();
            cellResult.CellReference = address;

            row.InsertBefore(cellResult, refCell);
            return cellResult;
        }

        /// <summary>
        /// Return the row at the specified rowIndex located within
        /// the sheet data passed in via wsData. If the row does not
        /// exist, create it.
        /// </summary>
        /// <param name="wsData"></param>
        /// <param name="rowIndex"></param>
        /// <returns></returns>
        private Row GetRow(SheetData wsData, UInt32 rowIndex)
        {
            var row = wsData.Elements<Row>()
                            .Where(r => r.RowIndex.Value == rowIndex)
                            .FirstOrDefault();
            if (row == null)
            {
                row = new Row();
                row.RowIndex = rowIndex;
                wsData.Append(row);
            }
            return row;
        }

        /// <summary>
        /// Given an Excel address such as E5 or AB128, GetRowIndex
        /// parses the address and returns the row index.
        /// </summary>
        /// <param name="address"></param>
        /// <returns></returns>
        private UInt32 GetRowIndex(string address)
        {
            // Create a regular expression to match the row index portion the cell name.
            var regex = new Regex(@"\d+");
            var match = regex.Match(address);

            return uint.Parse(match.Value);
        }

        /// <summary>
        /// Given a cell name, parses the specified cell to get the column name.
        /// </summary>
        /// <param name="address"></param>
        /// <returns></returns>
        private string GetColumnName(string address)
        {
            // Create a regular expression to match the column name portion of the cell name.
            var regex = new Regex("[A-Za-z]+");
            var match = regex.Match(address);

            return match.Value;
        }
        
        /// <summary>
        /// Given the main workbook part, and a text value, insert the text into 
        /// the shared string table. Create the table if necessary. If the value 
        /// already exists, return its index. If it doesn't exist, insert it and 
        /// return its new index.
        /// </summary>
        /// <param name="wbPart"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        private int InsertSharedStringItem(WorkbookPart wbPart, string value)
        {
            int index = 0;
            bool found = false;
            var stringTablePart = wbPart
                .GetPartsOfType<SharedStringTablePart>().FirstOrDefault();

            // If the shared string table is missing, something's wrong.
            // Just return the index that you found in the cell.
            // Otherwise, look up the correct text in the table.
            if (stringTablePart == null)
            {
                // Create it.
                stringTablePart = wbPart.AddNewPart<SharedStringTablePart>();
            }

            var stringTable = stringTablePart.SharedStringTable;
            if (stringTable == null)
            {
                stringTable = new SharedStringTable();
            }

            // Iterate through all the items in the SharedStringTable. 
            // If the text already exists, return its index.
            foreach (SharedStringItem item in stringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == value)
                {
                    found = true;
                    break;
                }
                index += 1;
            }

            if (!found)
            {
                stringTable.AppendChild(new SharedStringItem(new Text(value)));
                stringTable.Save();
            }

            return index;
        }

        #endregion

        #region IDispose
        public void Dispose()
        {
            Document.Close();
        }
        #endregion

        #region Static
        private static Stylesheet CreateStylesheet()
        {
            var stylesheet = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
            stylesheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            stylesheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");

            var fonts1 = new Fonts() { Count = (UInt32Value)1U, KnownFonts = true };

            // regular
            var font0 = new Font();
            font0.Append(new FontName() { Val = "Courier New" });
            font0.Append(new FontFamilyNumbering() { Val = 2 });

            // header
            var font1 = new Font();
            font1.Append(new FontName() { Val = "Courier New" });
            font1.Append(new FontFamilyNumbering() { Val = 2 });
            font1.Append(new Bold());

            var font2 = new Font();
            font2.Append(new Color() { Rgb = "FFFF0000" });

            var font3 = new Font();
            font3.Append(new Color() { Rgb = "FF0070C0" });

            fonts1.Append(font0);
            fonts1.Append(font1);
            fonts1.Append(font2);
            fonts1.Append(font3);

            var fills1 = new Fills() { Count = (UInt32Value)5U };

            // FillId = 0
            var fill1 = new Fill();
            var patternFill1 = new PatternFill() { PatternType = PatternValues.None };
            fill1.Append(patternFill1);

            // FillId = 1
            var fill2 = new Fill();
            var patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };
            fill2.Append(patternFill2);

            // FillId = 2,RED
            var fill3 = new Fill();
            var patternFill3 = new PatternFill() { PatternType = PatternValues.Solid };
            var foregroundColor1 = new ForegroundColor() { Rgb = "FFFF0000" };
            var backgroundColor1 = new BackgroundColor() { Rgb = "FFFF0000" };
            patternFill3.Append(foregroundColor1);
            //patternFill3.Append(backgroundColor1);
            fill3.Append(patternFill3);

            // FillId = 3,BLUE
            var fill4 = new Fill();
            var patternFill4 = new PatternFill() { PatternType = PatternValues.Solid };
            var foregroundColor2 = new ForegroundColor() { Rgb = "FF0070C0" };
            var backgroundColor2 = new BackgroundColor() { Indexed = (UInt32Value)64U };
            patternFill4.Append(foregroundColor2);
            patternFill4.Append(backgroundColor2);
            fill4.Append(patternFill4);

            // FillId = 4,YELLO
            var fill5 = new Fill();
            var patternFill5 = new PatternFill() { PatternType = PatternValues.Solid };
            var foregroundColor3 = new ForegroundColor() { Rgb = "FFFFFF00" };
            var backgroundColor3 = new BackgroundColor() { Indexed = (UInt32Value)64U };
            patternFill5.Append(foregroundColor3);
            patternFill5.Append(backgroundColor3);
            fill5.Append(patternFill5);

            fills1.Append(fill1);
            fills1.Append(fill2);
            fills1.Append(fill3);
            fills1.Append(fill4);
            fills1.Append(fill5);

            var borders1 = new Borders() { Count = (UInt32Value)1U };

            var border1 = new Border();
            var leftBorder1 = new LeftBorder();
            var rightBorder1 = new RightBorder();
            var topBorder1 = new TopBorder();
            var bottomBorder1 = new BottomBorder();
            var diagonalBorder1 = new DiagonalBorder();

            border1.Append(leftBorder1);
            border1.Append(rightBorder1);
            border1.Append(topBorder1);
            border1.Append(bottomBorder1);
            border1.Append(diagonalBorder1);

            borders1.Append(border1);

            var cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U };
            var cellStyleFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
            cellStyleFormats1.Append(cellStyleFormat1);

            var cellFormats1 = new CellFormats() { Count = (UInt32Value)4U };

            var regularAlignment = new Alignment()
                {
                    Horizontal = new EnumValue<HorizontalAlignmentValues>(HorizontalAlignmentValues.Left),
                    Vertical = new EnumValue<VerticalAlignmentValues>(VerticalAlignmentValues.Top),
                    WrapText = true
                };
            var cellFormat0 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, Alignment = regularAlignment, ApplyAlignment = true };
            var cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
            var cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
            var cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
            var cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };

            cellFormats1.Append(cellFormat0);
            cellFormats1.Append(cellFormat1);
            cellFormats1.Append(cellFormat2);
            cellFormats1.Append(cellFormat3);
            cellFormats1.Append(cellFormat4);

            var cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };
            var cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };

            cellStyles1.Append(cellStyle1);
            var differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U };
            var tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9" };

            var stylesheetExtensionList1 = new StylesheetExtensionList();

            StylesheetExtension stylesheetExtension1 = new StylesheetExtension() { Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };
            stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
            var slicerStyles1 = new Excel.SlicerStyles() { DefaultSlicerStyle = "SlicerStyleLight1" };

            stylesheetExtension1.Append(slicerStyles1);

            stylesheetExtensionList1.Append(stylesheetExtension1);

            stylesheet.Append(fonts1);
            stylesheet.Append(fills1);
            stylesheet.Append(borders1);
            stylesheet.Append(cellStyleFormats1);
            stylesheet.Append(cellFormats1);
            stylesheet.Append(cellStyles1);
            stylesheet.Append(differentialFormats1);
            stylesheet.Append(tableStyles1);
            stylesheet.Append(stylesheetExtensionList1);
            return stylesheet;
        }    
        #endregion
    }
}
